ActiveReports 11
Recursive Hierarchy Reports
ActiveReports 11 > ActiveReports User Guide > Samples and Walkthroughs > Walkthroughs > Page Report/RDL Report Walkthroughs > Layout > Recursive Hierarchy Reports
Show All
Hide All

You can create a report using a recursive hierarchy and the Level function to show parent-child relationships in data. This walkthrough illustrates how to create a recursive hierarchy report.

The walkthrough is split into the following activities:

Note:

When you complete this walkthrough you get a layout that looks similar to the following at design time and at run time.

Design-Time Layout

Run-Time Layout

To add an ActiveReport to the Visual Studio project

  1. Create a new Visual Studio project.
  2. From the Project menu, select Add New Item.
  3. In the Add New Item dialog that appears, select ActiveReports 11 Page Report and in the Name field, rename the file as rptRecursiveHierarchy.
  4. Click the Add button to open a new fixed page report in the designer.

See Adding an ActiveReport to a Project for information on adding different report layouts.

To connect the report to a data source

  1. In the Report Explorer, right-click the Data Sources node and select the Add Data Source option or select Data Source from the Add button.
  2. In the Report Data Source Dialog that appears, select the General page and in the Name field, enter a name like ReportData.
  3. On this page, create a connection to the Reels database. See Connect to a Data Source for information on connecting to a data source.

To create a dataset to populate the parameter values

  1. In the Report Explorer, right-click the data source node and select the Add Data Set option or select Data Set from the Add button.
  2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as Stores. This name appears as a child node to the data source icon in the Report Explorer.
  3. On the Query page of this dialog, in the Query field enter the following SQL query.
    SQL Query
    Copy Code 
    SELECT StoreID FROM Store
  4. Click the Validate DataSet icon at the top right hand corner above the Query box to validate the query.
  5. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

To add a report parameter

  1. In the Report Explorer, right-click the data source node and select the Parameters node or select Parameter from the Add button.
  2. In the Report - Parameters dialog that appears, set the following values:

    In the General tab

    • Name: StoreID
    • DataType: Integer
    • Text for prompting users for a value: Select a store number

    In the Available Values tab select From query

    • Dataset: Stores
    • Value: StoreID
    • Label: StoreID
  3. Click OK to close the dialog and add the parameter under the Parameters node of the Report Explorer.

To add a dataset for the report

  1. In the Report Explorer, right-click the data source node and select the Add Data Set option.
  2. In the DataSet Dialog that appears, select the General page and enter the name of the dataset as Employees. This name appears as a child node to the data source icon in the Report Explorer.
  3. On the Parameters page, add a parameter with the following properties.
    • Parameter Name: @StoreID
    • Value: =Parameters!StoreID.Value
  4. On the Query page of this dialog, change the Command Type to StoredProcedure and enter the following stored procedure into the Query text box (the question mark denotes the parameter)
    StoredProcedure Query
    Copy Code
    EmployeesForStore ?
    EmployeesForStore ?
  5. Click the Validate icon to validate the query and to populate the Fields list.
  6. Click OK to close the dialog. Your data set and queried fields appear as nodes in the Report Explorer.

To create a layout for the report

  1. From the toolbox, drag a Table control onto the body of the report and go to the Properties window to set the following properties.
    Property Name Property Value
    Location 0in, 0in
    DataSetName Employees
    FixedSize (only for Page reports) 6.5in, 9in
  2. Click inside the table to display the row and column handles along the left and top edges of the table.
  3. Select the columns by clicking the grey column header above the column and change the Width property in the order as follows.
    Column Property Name
    Second Column Width: 1.5in
    Third Column Width: 1.05in
    First Column Width: 2.2in
  4. Right-click the grey column header above the third column and select Insert Column to the Right.
  5. Select the fourth column and change its Width property to 1.75in.
    Tip: Making some columns narrower before adding columns or making other columns wider prevents your report width from changing.
  6. In the Report Explorer from the Employees dataset, drag the following fields onto the detail row of the table.
    Data Field Column Name
    Title TableColumn1
    LastName TableColumn2
    Supervisor TableColumn3
    Department TableColumn4
  7. Select the LastName field in the second column of the detail row and in the Properties window, set the Value property to =Fields!LastName.Value & " " & Fields!EmployeeID.Value. This will display the Employee ID number along with each employee's last name.
  8. Select the static label in the second column of the header row of the table and in the properties window, set its Value property to Last Name and ID.
  9. Select the Supervisor field in the third column of the detail row and in the Properties window, set its TextAlign property to Center.
  10. Select the static label in the third column of the detail row and in the Properties window, set its Value property to Supervisor ID.
  11. Select the header row by clicking the table handle to the left of the row and in the Properties window, set the following properties.
    Property Name Property Value
    TextAlign Center
    FontWeight Bold
    BackgroundColor DarkSlateBlue
    Color White
  12. Select the detail row and in the Properties window, set the BorderStyle property to Solid.
  13. Right-click the table handle to the left of the header row and select Insert Row Above.
  14. While holding down the CTRL key, click each of the cells in the newly added top row.
  15. Right-click inside the selected cells and select Merge Cells to create a cell that spans the table.
  16. Go to the Properties window to set the following properties.
    Property Name Property Value
    TextAlign Center
    FontSize 14pt
    Value ="Store Number " & Parameters!StoreID.Value
  17. Right-click the table handle to the left of the row and select Table Footer to remove the footer row from the table.
Note: In case you are setting a recursive hierarchy on a Page report, set the DataSetName property in the FixedPage Dialog to Employees.

To set up a recursive hierarchy

  1. Right-click the table handle to the left of the Detail row and select Edit Group to open the Table-Detail Grouping dialog.
  2. Under Group on: Expression, select =Fields!EmployeeID.Value.
  3. Under Parent group:, select =Fields!Supervisor.Value.
  4. Click OK to close the dialog.

To use the Level function to display the hierarchy

  1. Select the cell in the first column of the Detail row that reads =Fields!Title.Value and in the Properties window, expand the Padding property.
  2. In the Padding > Left property, enter the expression =2 + (Level() * 15) & "pt".
    Note: Adding 2 to the result ensures that a normal amount of padding is always used.

To use the Level function with themes in the BackgroundColor property of a textbox

  1. Go to C:\Users\<User>\Documents\GrapeCity Samples\ActiveReports 11\Reports Gallery\<C# or VB.NET>\Reports\RDL Report\Reels.
  2. Copy the file Reels.rdlx-theme and paste it into the folder in which you saved your report.
  3. In the Report Explorer, select Report.
  4. In the Properties window in the Theme property, enter the theme file name: Reels.rdlx-theme.
  5. Click the table handle to the left of the detail row to select the entire row.
  6. In the Properties window, set the BackgroundColor property to =Theme.Colors(Level() + 1, 4).

To view the report

OR

See Also

Concepts

Expressions